using Castle.Core.Internal;
using Confluent.Kafka;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Internal;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Logical;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using SkiaSharp;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using VOL.Core.DBManager;
using VOL.Core.Extensions;
using VOL.Entity.DomainModels;

namespace VOL.Finance.Controllers
{
    [Route("api/synchronization")]
    [ApiController]
    public partial class cw_djlc_synchronizationController : Controller
    {
        /// <summary>
        /// 这个方法的主要用处就是
        ///     产品定价流程中差异分析、差异分析汇总中的数据需要存入到表中
        ///     需要考虑到的情景有：
        ///     1、正常创建流程时  没有对标物料BOM、需求物料BOM未同步
        ///                        没有对标物料BOM，但需求物料BOM未同步到数据
        ///                        没有对标物料BOM，已同步到需求物料BOM
        ///                        对标物料BOM已维护，需求物料BOM未同步
        ///                        对标物料BOM已维护，但需求物料BOM未同步到数据
        ///                        对标物料BOM已维护，已同步到需求物料BOM
        ///     2、当流程开始流转后 采购没有调整价格异议
        ///                         采购调整价格异议
        ///     所以针对以上的情景，在进行同步的时候需要考虑到：
        ///         不管是对标物料还是需求物料可能都会在同步一次后新增物料或者更改价格
        ///         对于对标物料号在流程stpid为10，is_have_bom = 1需要同步，因为在创建新流程时可能选择已有BOM的对标物料
        ///         对于对标物料号在流程stpid为20或30，is_have_bom = 1需要同步，
        ///             因为这个stpid为20/30是因为担心在执行定时任务的时候stpid为20的时候没有执行到。所以增加stpid为30
        ///                 对于对标物料BOM并不会进行修改，因为已经定过价。所以需要判断该定价流程编码下的对标BOM是否已经同步
        ///         
        ///         对于需求物料号则可以在stpid为10、20、30，
        ///                               synchronizationbomstatus == 1 
        ///                               synchronizationpricestatus == 1 
        ///                               is_synchronized_bom == 1时
        ///             就可以进行同步
        /// </summary>
        /// <returns></returns>
        [HttpGet, Route("synchronization"), AllowAnonymous]//产品定价取SAPBOM和价格后台job
        public dynamic synchronization()
        {
            // 获取满足条件的产品定价流程
             List<cw_djlc_djzb> cw_djlc_djzbList = DBServerProvider.DbContext.Set<cw_djlc_djzb>().Where(x => x.stepid != 99 && x.stepid > 20 && (x.is_have_bom == 1 || x.is_synchronized_bom == 1)).ToList();
            //List<cw_djlc_djzb> cw_djlc_djzbList = DBServerProvider.DbContext.Set<cw_djlc_djzb>().Where(x => x.stepid == 99  && (x.is_have_bom == 1 || x.is_synchronized_bom == 1)).ToList();
            // List<cw_djlc_djzb> cw_djlc_djzbList = DBServerProvider.DbContext.Set<cw_djlc_djzb>().Where(x => x.cw_djlc_code == "cw_djlc_20240111_02").ToList();

            // 获取对应的生产差异分析sql语句
            execute_sql_dictionary execute_sql_dictionary = DBServerProvider.DbContext.Set<execute_sql_dictionary>().Where(x => (x.classify_route == "synchronization")).FirstOrDefault();
            // 获取差异分析汇总sql语句
            execute_sql_dictionary execute_sql_dictionaryTotal = DBServerProvider.DbContext.Set<execute_sql_dictionary>().Where(x => (x.classify_route == "synchronizationTotal")).FirstOrDefault();
            
            string vinappend = "(";
            foreach (cw_djlc_djzb cw_djlc_djzb in cw_djlc_djzbList)
            {
                vinappend += "'" + cw_djlc_djzb.cw_djlc_code + "',";
            }
            vinappend = vinappend.Substring(0, vinappend.Length - 1) + ")";
            //拼接sql
            execute_sql_dictionary.sql = execute_sql_dictionary.sql + vinappend;
            execute_sql_dictionary.sql = execute_sql_dictionary.sql.Replace("''", "'");
            
            execute_sql_dictionaryTotal.sql = execute_sql_dictionaryTotal.sql + vinappend;
            execute_sql_dictionaryTotal.sql = execute_sql_dictionaryTotal.sql.Replace("''", "'");
            // 将SQL到10.100.150.18库中去查询
            List<cw_djlc_analyze_total_table> dataTotalList = DBServerProvider.SqlDapper.QueryList<cw_djlc_analyze_total_table>(execute_sql_dictionaryTotal.sql, "").ToList();
            // 差异分析汇总
            foreach (cw_djlc_analyze_total_table item in dataTotalList)
            {
                cw_djlc_analyze_total_table cw_djlc_analyze_total_table = DBServerProvider.DbContext.Set<cw_djlc_analyze_total_table>().Where(x => x.cw_djlc_code == item.cw_djlc_code && x.groupnum == item.groupnum).FirstOrDefault();
                if (cw_djlc_analyze_total_table.IsNullOrEmpty())
                {
                    cw_djlc_analyze_total_table cw_djlc_analyze_total_table1 = new cw_djlc_analyze_total_table();
                    cw_djlc_analyze_total_table1.cw_djlc_analyze_total_id = Guid.NewGuid();
                    cw_djlc_analyze_total_table1.cw_djlc_djzb_id = item.cw_djlc_djzb_id;
                    cw_djlc_analyze_total_table1.cw_djlc_code = item.cw_djlc_code;
                    cw_djlc_analyze_total_table1.groupnum = item.groupnum;
                    cw_djlc_analyze_total_table1.groupnumname = item.groupnumname;
                    cw_djlc_analyze_total_table1.oldprice = item.oldprice;
                    cw_djlc_analyze_total_table1.newprice = item.newprice;
                    cw_djlc_analyze_total_table1.analyzeprice = item.analyzeprice;
                    cw_djlc_analyze_total_table1.Creator = "新增";
                    cw_djlc_analyze_total_table1.CreateDate = DateTime.Now;
                    DBServerProvider.DbContext.Add(cw_djlc_analyze_total_table1);
                }
                else
                {
                    cw_djlc_analyze_total_table.cw_djlc_djzb_id = item.cw_djlc_djzb_id;
                    cw_djlc_analyze_total_table.cw_djlc_code = item.cw_djlc_code;
                    cw_djlc_analyze_total_table.groupnum = item.groupnum;
                    cw_djlc_analyze_total_table.groupnumname = item.groupnumname;
                    cw_djlc_analyze_total_table.oldprice = item.oldprice;
                    cw_djlc_analyze_total_table.newprice = item.newprice;
                    cw_djlc_analyze_total_table.analyzeprice = item.analyzeprice;
                    cw_djlc_analyze_total_table.Modifier = "修改";
                    cw_djlc_analyze_total_table.ModifyDate = DateTime.Now;
                    DBServerProvider.DbContext.Update(cw_djlc_analyze_total_table);
                }
            }

            List<cw_djlc_analyze_table> dataList = DBServerProvider.SqlDapper.QueryList<cw_djlc_analyze_table>(execute_sql_dictionary.sql, "").ToList();
            // 差异分析明细
            foreach (cw_djlc_analyze_table item in dataList)
            {
                ///     根据产品定价流程、对标物料子件编号、需求物料子件编号判断是否有数据
                ///     如果有则更新
                ///     如果没有则插入
                var sql = "SELECT * from cw_djlc_analyze_table WHERE cw_djlc_code =  '" + item.cw_djlc_code + "' AND ( oldcomponentnum = '"+ item.oldcomponentnum + "' or  newcomponentnum = '"+ item.newcomponentnum + "') and groupnum = '"+item.groupnum+"'";
                cw_djlc_analyze_table cw_djlc_analyze_table = DBServerProvider.SqlDapper.QueryList<cw_djlc_analyze_table>(sql, "").FirstOrDefault();
               // cw_djlc_analyze_table cw_djlc_analyze_table1 = DBServerProvider.DbContext.Set<cw_djlc_analyze_table>().Where(x => x.cw_djlc_code == item.cw_djlc_code && x.oldcomponentnum == item.oldcomponentnum && x.newcomponentnum == item.newcomponentnum).FirstOrDefault();
               if (cw_djlc_analyze_table.IsNullOrEmpty())
                {
                    cw_djlc_analyze_table cw_djlc_analyze_table1 = new cw_djlc_analyze_table();
                    cw_djlc_analyze_table1.cw_djlc_analyze_id = Guid.NewGuid();
                    cw_djlc_analyze_table1.cw_djlc_djzb_id = item.cw_djlc_djzb_id;
                    cw_djlc_analyze_table1.cw_djlc_code = item.cw_djlc_code;
                    cw_djlc_analyze_table1.oldcomponentnum = item.oldcomponentnum;
                    cw_djlc_analyze_table1.olddescription = item.olddescription;
                    cw_djlc_analyze_table1.oldnum = item.oldnum;
                    cw_djlc_analyze_table1.oldUnitprice = item.oldUnitprice;
                    cw_djlc_analyze_table1.oldprice = item.oldprice;
                    cw_djlc_analyze_table1.newcomponentnum = item.newcomponentnum;
                    cw_djlc_analyze_table1.newdescription  = item.newdescription;
                    cw_djlc_analyze_table1.newnum = item.newnum;
                    cw_djlc_analyze_table1.newUnitprice =item.newUnitprice;
                    cw_djlc_analyze_table1.newprice = item.newprice;
                    cw_djlc_analyze_table1.analyzeprice = item.analyzeprice;
                    cw_djlc_analyze_table1.groupnum = item.groupnum;
                    cw_djlc_analyze_table1.groupnumname = item.groupnumname;
                    cw_djlc_analyze_table1.Creator = "新增";
                    cw_djlc_analyze_table1.CreateDate = DateTime.Now;
                    DBServerProvider.DbContext.Add(cw_djlc_analyze_table1);
                }
                else
                {
                    var sql1 = "update cw_djlc_analyze_table set newprice = @newprice,newUnitprice=@newUnitprice,analyzeprice =@analyzeprice,Modifier='修改',ModifyDate=@ModifyDate where cw_djlc_analyze_id = @cw_djlc_analyze_id";
                    decimal analyzeprice = 0;
                    if (cw_djlc_analyze_table.oldprice.IsNullOrEmpty())
                    {
                        analyzeprice = (decimal)item.newprice - 0;
                    }
                    else
                    {
                        if (item.newprice.IsNullOrEmpty())
                        {
                            analyzeprice = (decimal)(0 - cw_djlc_analyze_table.oldprice);
                        }
                        else
                        {
                            analyzeprice = (decimal)(item.newprice - cw_djlc_analyze_table.oldprice);
                        }
                    }
                    var ModifyDate = DateTime.Now;
                    DBServerProvider.SqlDapper.ExcuteNonQuery(sql1, new { item.newprice, item.newUnitprice, analyzeprice, ModifyDate , cw_djlc_analyze_table.cw_djlc_analyze_id });
                }
            }
             DBServerProvider.DbContext.SaveChanges();
             List<Guid> cw_djlc_djzb_idList = new List<Guid>();
             dataList.ForEach(s => {
                 if (!cw_djlc_djzb_idList.Contains((Guid)s.cw_djlc_djzb_id))
                 {
                     cw_djlc_djzb_idList.Add((Guid)s.cw_djlc_djzb_id);
                 }
             });
             foreach (var item in cw_djlc_djzb_idList)
             {
                
                // 更新物料表 根据产品定价ID，查询到对应的BOM编号，及查询得到的对标物料子件编号、需求物料子件编号到物料表中进行反更
                cw_djlc_djzb cw_djlc_djzb = DBServerProvider.DbContext.Set<cw_djlc_djzb>().Where(x => x.cw_djlc_djzb_id == item).FirstOrDefault();
                var sql = "update cw_djlc_wlzb set is_synchronization_status = 1 where bomnumber = @bomnumber";
                // 在更改物料BOM状态之前需要先查一下在stpid<20时仍然有对应的物料存在，则就不能更改BOM状态
                List<cw_djlc_djzb> dataList1 = DBServerProvider.DbContext.Set<cw_djlc_djzb>().Where(x =>x.cw_djlc_code != cw_djlc_djzb.cw_djlc_code && x.stepid < 20 && x.oldbomnumber == cw_djlc_djzb.oldbomnumber ).ToList();
                if (dataList1.IsNullOrEmpty())
                {
                    //反更物料表- 对标物料
                    var bomnumber = cw_djlc_djzb.oldbomnumber;
                    DBServerProvider.SqlDapper.ExcuteNonQuery(sql, new { bomnumber });

                    //反更物料表- 对标物料
                    bomnumber = cw_djlc_djzb.newbomnumber;
                    DBServerProvider.SqlDapper.ExcuteNonQuery(sql, new { bomnumber });
                }
             }
             DBServerProvider.DbContext.SaveChanges();
            return dataList.Count;
        }
    }
}

